package com.amumu.netctoss.dao.impl;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.query.Query;
import org.springframework.context.annotation.Scope;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.amumu.netctoss.dao.BillDao;
import com.amumu.netctoss.entity.Bill;
import com.amumu.netctoss.vo.BillItem;
import com.amumu.netctoss.vo.ServiceDetail;

@Repository
@Scope("prototype")
public class HibernateBillDao extends HibernateBaseDao implements BillDao{
	
	public List<Bill> findList(int page,int size, String idcardNo, String loginName,
			String realName, String billMonth) throws Exception {
		String hql = "from Bill b join fetch b.account a where 1=1";
		List params = new ArrayList();
		if(idcardNo!=null&&!"".equals(idcardNo)){
			hql +=" and a.idcardNo=?";
			params.add(idcardNo.trim());
		}
		if(loginName!=null&&!"".equals(loginName)){
			hql +=" and a.loginName like ?";
			params.add("%"+loginName.trim()+"%");
		}
		if(realName!=null&&!"".equals(realName)){
			hql +=" and a.realName=?";
			params.add(realName.trim());
		}
		if(billMonth!=null&&!"".equals(billMonth)&&billMonth.length()<=6){
			hql +=" and b.billMonth like ?";
			params.add(billMonth);
		}
		return findByPage(page, size, hql, params.toArray());
	}

	public List<BillItem> findBillItems(int page,int size,int billId)
			throws Exception {
		final String sql = "select b.item_id,s.os_username,s.unix_host,a.id,sum(sd.duration),b.cost,c.name from bill_item b,service s,cost c,service_detail sd,account a,bill bi where b.service_id=s.id and s.cost_id=c.id and sd.service_id=s.id and a.id=s.account_id and bi.id=? and to_char(sd.logout_time,'yyyymm')=bi.bill_month group by b.item_id,s.os_username,s.unix_host,a.id,c.name,b.cost";
		
		List<Object[]> list = findByPageSQL(page, size, sql, billId);
		return toBillItems(list);		
	}

	public List<ServiceDetail> findServiceDetail(int page, int size,final int itemId)
			throws Exception {
		final String sql ="select sd.client_host,sd.login_time,sd.logout_time,sd.duration,sd.cost,c.name from service_detail sd,service s,cost c,bill_item bi,bill b where sd.service_id=s.id and s.cost_id=c.id and bi.service_id=s.id and b.id=bi.bill_id and to_char(sd.logout_time,'yyyymm')=b.bill_month and bi.item_id=?";
		List<Object[]> list =  findByPageSQL(page, size, sql, itemId);
		return toServiceDetails(list);
	}
	
	public Bill findBillById(int id) throws Exception {
		return (Bill) getHibernateTemplate().get(Bill.class, id);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public BillItem findBillItemById(final int id) throws Exception {
		final String sql = "select b.item_id,s.os_username,s.unix_host,a.id,sum(sd.duration),b.cost,c.name from bill_item b,service s,cost c,service_detail sd,account a,bill bi where b.service_id=s.id and s.cost_id=c.id and sd.service_id=s.id and a.id=s.account_id and b.item_id=? and to_char(sd.logout_time,'yyyymm')=bi.bill_month group by b.item_id,s.os_username,s.unix_host,a.id,c.name,b.cost";
		List list =  (List) getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(sql);
				query.setParameter(0, id);
				
				List<Object[]> list = query.list();
				return toBillItems(list);
			}
		});
		return (BillItem) list.get(0);
	}
	
	
	public int getBillPages(int size, String idcardNo, String loginName,
			String realName, String billMonth) throws Exception {
		
		String hql = "select count(*) from Bill b join b.account a where 1=1";
		List params = new ArrayList();
		if(idcardNo!=null&&!"".equals(idcardNo)){
			hql +=" and a.idcardNo=?";
			params.add(idcardNo.trim());
		}
		if(loginName!=null&&!"".equals(loginName)){
			hql +=" and a.loginName like ?";
			params.add("%"+loginName.trim()+"%");
		}
		if(realName!=null&&!"".equals(realName)){
			hql +=" and a.realName=?";
			params.add(realName.trim());
		}
		if(billMonth!=null&&!"".equals(billMonth)&&billMonth.length()<=6){
			hql +=" and b.billMonth like ?";
			params.add(billMonth);
		}
		return getTotalPage(size, hql, params.toArray());
	}

	public int getDetailsPages(int size, int itemId) throws Exception {
		
		String sql ="select count(sd.id) from service_detail sd,service s,cost c,bill_item bi,bill b where sd.service_id=s.id and s.cost_id=c.id and bi.service_id=s.id and b.id=bi.bill_id and to_char(sd.logout_time,'yyyymm')=b.bill_month and bi.item_id=?";
		return getTotalPageSQL(size, sql, itemId);
	}

	public int getItemPages(int size, int billId) throws Exception {
		String sql = "select count(distinct b.item_id) from bill_item b,service s,cost c,service_detail sd,account a,bill bi where b.service_id=s.id and s.cost_id=c.id and sd.service_id=s.id and a.id=s.account_id and bi.id=? and to_char(sd.logout_time,'yyyymm')=bi.bill_month ";
		
		return getTotalPageSQL(size, sql, billId);
	}


	private List<BillItem> toBillItems(List<Object[]> list){
		List<BillItem> items = new ArrayList<BillItem>();
		for(Object[] o:list){
			BillItem it = new BillItem();
			System.out.println(o[0]);
			it.setId(Integer.parseInt(o[0].toString()));
			it.setOsUsername((String) o[1]);
			it.setHostIp((String) o[2]);
			it.setAccountId(Integer.valueOf(o[3].toString()));
			it.setDuration(Long.valueOf(o[4].toString()));
			it.setCost(Double.valueOf(o[5].toString()));
			it.setCostName((String) o[6]);
			
			items.add(it);
		}
		return items;
	}
	private List<ServiceDetail> toServiceDetails(List<Object[]> list){
		
		List<ServiceDetail> sds = new ArrayList<ServiceDetail>();
		
		for(Object[] o:list){
			ServiceDetail sd = new ServiceDetail();
			sd.setClientHost((String)o[0]);
			sd.setLoginTime((Timestamp) o[1]);
			sd.setLogoutTime((Timestamp) o[2]);
			sd.setDuration(Long.valueOf(o[3].toString()));
			sd.setCost(Double.valueOf(o[4].toString()));
			sd.setCostName(o[5].toString());
			
			sds.add(sd);
		}
		return sds;
	}

}
